<?php if ( ! defined('BASEPATH')) exit('No direct script access allowed');

class Arrear_summary_model extends CI_Model
{
    public function __construct()
    {
        parent::__construct();
    }


   public function get_arrears(){
    $sql="SELECT tbl.area,
        COUNT(if(tbl.a_count='2',1,NULL)) as c2,
        COUNT(if(tbl.a_count='3',1,NULL)) as c3,
        COUNT(if(tbl.a_count='4',1,NULL)) as c4,
        COUNT(if(tbl.a_count>4,1,NULL)) as above4,
        COUNT(*) as actives
    FROM (
                    SELECT aea.zone_1 area
                      ,aa.acct_num num
                      ,aa.acct_name name
                        ,count(x.customer_id) a_count
                    FROM ar_account aa
                    INNER JOIN ar_entity_address aea
                          ON aa.acct_addr = aea.address_id
                    LEFT JOIN (SELECT customer_id FROM ubs_invoices_payments_v WHERE ifnull(sum_pay,0) != ifnull(invoice_total,0)) x ON x.customer_id = aa.account_id
                    WHERE aa.enable_flag = 'Y' AND aa.status ='ACT'
                    AND LAST_DAY(aa.active_start)
                      BETWEEN LAST_DAY(aa.active_start)
                      AND IFNULL(LAST_DAY(aa.active_end), LAST_DAY(NOW()))
                      GROUP BY aea.zone_1,aa.acct_num,aa.acct_name 
                  ) tbl
                  GROUP BY tbl.area
                  ORDER BY tbl.a_count ASC";

    $query = $this->db->query($sql);
    return $query->result();
   

   }


   public function get_names($id,$zone){
    $where = "";
    if($id == 5){
      $where = " WHERE a_count > 4 AND area = '".$zone."' ";
    }else{
      $where = " WHERE a_count = ".$id." AND area = '".$zone."' ";
    }


    $q = $this->db->query("SELECT tbl.area,tbl.name,tbl.num,tbl.a_count FROM (

                    SELECT aea.zone_1 area

                      ,aa.acct_num num

                      ,aa.acct_name name

                        ,count(x.customer_id) a_count

                    FROM ar_account aa

                    INNER JOIN ar_entity_address aea

                          ON aa.acct_addr = aea.address_id

                    LEFT JOIN (SELECT customer_id FROM ubs_invoices_payments_v WHERE (ifnull(sum_pay,0) != ifnull(invoice_total,0) AND ifnull(sum_pay,0) <= ifnull(invoice_total,0))) x ON x.customer_id = aa.account_id

                    WHERE aa.enable_flag = 'Y' and aa.status = 'ACT'

                    AND LAST_DAY(aa.active_start)

                      BETWEEN LAST_DAY(aa.active_start)

                      AND IFNULL(LAST_DAY(aa.active_end), LAST_DAY(NOW()))

                      GROUP BY aea.zone_1,aa.acct_num,aa.acct_name 

                  ) tbl

                  ".$where." 

                  GROUP BY num ");

    return $q->result();

  }


  public function get_pnd($id,$zone){
    $where = "";
    if($id == 5){
      $where = " WHERE a_count > 4 AND area = '".$zone."' ";
    }else{
      $where = " WHERE a_count = ".$id." AND area = '".$zone."' ";
    }


    $q = $this->db->query("SELECT aea.zone_1 AREA, 
                                  aa.acct_num num, 
                                  aa.acct_name NAME, 
                                  uipv.invoice_num inv_num, 
                                  uipv.invoice_date inv_date, 
                                  (IFNULL(uipv.invoice_total,0) - IFNULL(uipv.sum_pay,0)) balance,
                                  uipv.due_date due_date
                           FROM ubs_invoices_payments_v uipv

                INNER JOIN ar_account aa ON aa.account_id = uipv.customer_id

                  INNER JOIN ar_entity_address aea ON aea.address_id = aa.acct_addr 

                  WHERE (IFNULL(sum_pay,0) != IFNULL(invoice_total,0) AND ifnull(sum_pay,0) <= ifnull(invoice_total,0))

                  AND aa.acct_num IN (SELECT  tbl.num FROM (

                    SELECT aea.zone_1 area

                      ,aa.acct_num num

                      ,aa.acct_name name

                        ,count(x.customer_id) a_count

                    FROM ar_account aa

                    INNER JOIN ar_entity_address aea

                          ON aa.acct_addr = aea.address_id

                    LEFT JOIN (SELECT customer_id FROM ubs_invoices_payments_v WHERE (ifnull(sum_pay,0) != ifnull(invoice_total,0) AND ifnull(sum_pay,0) <= ifnull(invoice_total,0))) x ON x.customer_id = aa.account_id

                    WHERE aa.enable_flag = 'Y' and aa.status = 'ACT'

                    AND LAST_DAY(aa.active_start)

                      BETWEEN LAST_DAY(aa.active_start)

                      AND IFNULL(LAST_DAY(aa.active_end), LAST_DAY(NOW()))

                      GROUP BY aea.zone_1,aa.acct_num,aa.acct_name 

                  ) tbl

                  ".$where." 

                  GROUP BY num)

                  ORDER BY uipv.invoice_date DESC");

    return $q->result();

  }

   
}